import pymysql


# 配置数据库连接
def getconnnect():
    connection = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
        database='sentences_tags',
        cursorclass=pymysql.cursors.DictCursor
    )
    return connection


def find_all(connection):
    try:
        with connection.cursor() as cursor:
            # 执行查询
            sql = "SELECT * FROM sentences_tags.tbl_sentence_tag"
            cursor.execute(sql)
            result = cursor.fetchall()

            for row in result:
                print(row)
    finally:
        connection.close()


def insert(connection, contents):
    try:
        with connection.cursor() as cursor:  # 插入数据的 SQL 语句
            sql_insert = "INSERT INTO sentences_tags.tbl_sentence_tag (sentence, tag) VALUES (%s, %s)"
            cursor.executemany(sql_insert, contents)
            connection.commit()

            # 可选：查询并打印刚插入的数据以验证
            # sql_select = "SELECT * FROM sentences_tags.tbl_sentence_tag "  # 获取最后插入的3条记录
            # cursor.execute(sql_select)
            # result = cursor.fetchall()
            # for row in result:
            #     print(row)

    finally:
        # 关闭数据库连接
        connection.close()


def delete_table(connection):
    # 假设 connection 是一个有效的数据库连接
    try:
        with connection.cursor() as cursor:
            # 清空表的 SQL 语句
            sql_delete = "DELETE FROM sentences_tags.tbl_sentence_tag"

            # 执行清空操作
            cursor.execute(sql_delete)

            # 提交事务
            connection.commit()

            print("表数据已清空")

    finally:
        # 关闭数据库连接
        connection.close()


connection = getconnnect()
find_all(connection)

# 假设句子文档名为sentences.txt，标签文档名为labels.txt
sentences_file = '../data/all.words.txt'
labels_file = '../data/all.labels.txt'

# 用于存储转换后的句子和标签的列表
combined_data = []

# 读取句子文档并合并分词
with open(sentences_file, 'r', encoding='GBK') as f:
    sentences = f.readlines()
    merged_sentences = [sentence.replace(" ", "").strip() for sentence in sentences]  # 合并分词并去除首尾空格

# 读取标签文档并转换标签
with open(labels_file, 'r', encoding='GBK') as f:
    labels = f.readlines()
    # 将'pos'转换为1，'neg'转换为0，同时去除首尾空格和换行符
    binary_labels = [1 if label.strip() == 'POS' else 0 for label in labels]

# 确保句子和标签的数量匹配
assert len(merged_sentences) == len(binary_labels), "句子和标签的数量不匹配"

# 结合句子和标签
for sentence, label in zip(merged_sentences, binary_labels):
    combined_data.append((sentence, label))

connection = getconnnect()
insert(connection,combined_data)
